package com.javasm.dao.MisMannageDao.impl;



import com.javasm.dao.MisMannageDao.CountDao;
import com.javasm.entity.MisMannageEntity.BuyData;
import com.javasm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author:JAVASM
 * @description:
 * @date:2022/5/27 21:16
 * @version:0.1
 * @since:jdk11
 */
public class CountDaoImpl implements CountDao {

    @Override
    public List<BuyData> buyMonthData(String month) {
         Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) as peopleNum ,tpi.prod_name\n" +
                "from tb_buy tbu,tb_prod_info tpi\n" +
                "where tbu.fund_id=tpi.prod_id and DATE_FORMAT(tbu.buy_date,'%Y-%m')=?\n" +
                "group by tpi.prod_name \n" +
                "order by peopleNum desc");

        List<BuyData> buyMonthList = new ArrayList<BuyData>();

        try {
            ps = conn.prepareStatement(sb.toString());

            ps.setString(1,month);

            rs = ps.executeQuery();
                while (rs.next()) {
                    Integer peopleNum = rs.getInt("peopleNum");
                    String prodName = rs.getString("prod_name");
                    buyMonthList.add(new BuyData(prodName,peopleNum));
                    if (buyMonthList.size()>3){

                    return buyMonthList;
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return buyMonthList;
    }

    @Override
    public List<BuyData> buyFundData(Date queryDate) {
         Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) as peopleNum,tbu.buy_date,tpi.prod_name\n" +
                "from tb_buy tbu,tb_prod_info tpi\n" +
                "where tbu.fund_id=tpi.prod_id\n" +
                "and tbu.buy_date=?\n" +
                "group by tpi.prod_name");

        List<BuyData> buyDataList = new ArrayList<BuyData>();

        try {
            ps = conn.prepareStatement(sb.toString());

            java.sql.Date queryDateSql = new java.sql.Date(queryDate.getTime());
            ps.setDate(1, queryDateSql);

            rs = ps.executeQuery();

            while (rs.next()) {
                Integer peopleNum = rs.getInt("peopleNum");
                String prodName = rs.getString("prod_name");
                buyDataList.add(new BuyData(prodName,peopleNum));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return buyDataList;
    }
}
